package hms.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.hsqldb.jdbc.JDBCPreparedStatement;

/**
 * The DAO is the database access object that handles interaction between the
 * application code and the database
 */
public class DAO {

	private Connection conn; // database connection
	private String dbName = null; // database name
	private String dbFileLocation = ""; // database file location
	private String dbURL = null; // database url
	private boolean isSuccess = false;

	/**
	 * 
	 * Creates Connection to database specified by databaseName if databaseName
	 * doesn't exists, it is created.
	 * 
	 * Database operates in standalone mode
	 * 
	 * @param databaseName
	 *            database name
	 * @param databaseFileLocation
	 *            database file location;
	 *            <p>
	 *            Windows and UNIX file separators acceptable Enter empty string
	 *            to leave location relative to callee class/program
	 * @param ifExists
	 *            Check to see if current database exists at
	 *            databaseFileLocation
	 */
	public DAO(String databaseName, String databaseFileLocation,
			Boolean ifExists) {
		this.dbName = databaseName;
		this.dbFileLocation = databaseFileLocation;
		this.dbURL = "jdbc:hsqldb:" + dbFileLocation + dbName + ";ifexists="
				+ ifExists.toString();
	}

	/**
	 * Opening the database connection
	 * 
	 * @param ifExists
	 *            true - check to see if database exists
	 * @return true if statement executes successfully
	 */
	public boolean open() {
		try {
			Class.forName("org.hsqldb.jdbc.JDBCDriver");
			conn = DriverManager.getConnection(dbURL, "SA", "");
			isSuccess = true;
		} catch (Exception ex) {
			ex.printStackTrace(System.out);
			isSuccess = false;
		}
		return isSuccess;
	}

	/**
	 * Opening the database connection
	 * 
	 * @return true if statement executes successfully
	 */
	public boolean close() {
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("SHUTDOWN");
			conn.close();
			isSuccess = true;
		} catch (Exception ex) {
			ex.printStackTrace(System.out);
			isSuccess = false;
		}
		return isSuccess;
	}

	/**
	 * Returns the result set generated by executing the query statement on the
	 * database
	 * 
	 * @param queryString
	 *            database query statement
	 * @return the result set generated by executing the query statement on the
	 *         database
	 */
	public ResultSet getResult(String queryString) {
		ResultSet rs = null;
		try {
			Statement stmt = null;
			stmt = conn.createStatement();

			rs = stmt.executeQuery(queryString);

		} catch (Exception ex) {
			ex.printStackTrace(System.out);
		}
		return rs;
	}

	/**
	 * Executes CRUD (Create - Update - Delete) statements
	 * 
	 * @param queryString
	 * @return true if statement executes successfully
	 */
	public boolean updateCommand(String queryString) {
		try {
			Statement stmt = null;
			stmt = conn.createStatement();
			stmt.executeUpdate(queryString);
			isSuccess = true;
		} catch (Exception ex) {
			ex.printStackTrace(System.out);
			isSuccess = false;
		}
		return isSuccess;
	}

	public PreparedStatement generatePreparedStatement(String statement) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(statement);
		} catch (Exception ex) {
			ex.printStackTrace(System.out);
		}
		return ps;
	}

	public boolean executePreparedUpdate(PreparedStatement ps) {
		boolean success = true;
		try {
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		}
		return success;
	}

	public ResultSet executePreparedSelect(PreparedStatement ps) {
		ResultSet rs = null;
		try {
			rs = ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 
	 * Adds a value to a table and returns new id
	 * 
	 * @param dbTable
	 *            Table Name
	 * @param dbColumn
	 *            Table Column
	 * @param tblIDCol
	 *            Primary Key Column
	 * @param value
	 *            New Value To be added
	 * @return true newly created id if update was successful; -1 otherwise
	 */
	public int addDBTableValue(String dbTable, String dbColumn,
			String tblIDCol, String value) {
		int next_id = -1;
		String insertSQL = "INSERT INTO " + dbTable + "(" + dbColumn
				+ ") VALUES('" + value + "')";
		this.updateCommand(insertSQL);
		next_id = getDbId(tblIDCol, tblIDCol, dbColumn, value);
		return next_id;
	}

	/**
	 * 
	 * AddS a value to a table and returns new id
	 * 
	 * @param dbTable
	 *            Table Name
	 * @param dbColumn
	 *            Table Column
	 * @param tblIDCol
	 *            Primary Key Column
	 * @param value
	 *            New Value To be added
	 * @return true newly created id if update was successful; -1 otherwise
	 */
	public int addDBTableValueParam(String dbTable, String dbColumn,
			String tblIDCol, String value) {
		int next_id = -1;
		String insertSQL = "INSERT INTO " + dbTable + "(" + dbColumn
				+ ") VALUES(?)";
		try {
			PreparedStatement pstmt = this.generatePreparedStatement(insertSQL);
			pstmt.setString(1, value);
			pstmt.execute();
			next_id = getDbId(dbTable, dbColumn, tblIDCol, value);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			next_id = -1;
		}

		return next_id;
	}

	/**
	 * Exceptional Situations:
	 * - more than one ID for the same value
	 * - caught exception
	 * @param dbTable
	 *            Table Name
	 * @param dbColumn
	 *            Table Column
	 * @param tblIDCol
	 *            Primary Key Column
	 * @param value
	 *            New Value Added
	 * @return new ID or -1
	 */
	public int getDbId(String dbTable, String dbColumn, String tblIDCol,
			String value) {
		String nextIdSQL = "SELECT " + tblIDCol + " FROM " + dbTable
                + " WHERE " + dbColumn + " = ?";
		//System.out.println(nextIdSQL);
		int next_id = -1;
		PreparedStatement pstmt = this.generatePreparedStatement(nextIdSQL);
		
		try {	
			pstmt.setString(1, value);
			pstmt.execute();
			ResultSet rs= pstmt.getResultSet();
			rs.next();
			next_id = rs.getInt(tblIDCol);
			if (rs.next()) {
				next_id = -1;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			next_id = -1;
		}
		return next_id;
	}

	/**
	 * 
	 * Updates a value in row where tblIDCol = primKeyID
	 * 
	 * @param dbTable
	 *            Table Name to be updated
	 * @param dbColumn
	 *            Table Column to be updated
	 * @param tblIDCol
	 *            Primary Key Column
	 * @param value
	 *            New Value
	 * @param primKeyID
	 *            Primary Key Value for filtering
	 * @return true if update was successful
	 */
	public boolean updateDBTableValue(String dbTable, String dbColumn,
			String tblIDCol, String value, int primKeyID) {
		boolean isSuccess = false;
		String updateSQL = "UPDATE " + dbTable + " SET " + dbColumn + " = '"
				+ value + "' WHERE " + tblIDCol + " = " + primKeyID;
		isSuccess = this.updateCommand(updateSQL);
		return isSuccess;
	}

	/**
	 * 
	 * Updates a value in row where tblIDCol = primKeyID
	 * 
	 * @param dbTable
	 *            Table Name to be updated
	 * @param dbColumn
	 *            Table Column to be updated
	 * @param tblIDCol
	 *            Primary Key Column
	 * @param value
	 *            New Value
	 * @param primKeyID
	 *            Primary Key Value for filtering
	 * @return true if update was successful
	 */
	public boolean updateDBTableValueParam(String dbTable, String dbColumn,
			String tblIDCol, String value, int primKeyID) {
		boolean isSuccess = false;
		String updateSQL = "UPDATE " + dbTable + " SET " + dbColumn
				+ " = ? WHERE " + tblIDCol + " = ?";
		PreparedStatement pstmt;
		try {
			pstmt = this.conn.prepareStatement(updateSQL);
			pstmt.setString(1, value);
			pstmt.setInt(2, primKeyID);
			pstmt.execute();
			isSuccess = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			isSuccess = false;
		}
		return isSuccess;
	}
	
	/**
	 * 
	 * Updates a value in row where tblIDCol = primKeyID
	 * 
	 * @param dbTable
	 *            Table Name to be updated
	 * @param dbColumn
	 *            Table Column to be updated
	 * @param tblIDCol
	 *            Primary Key Column
	 * @param value
	 *            New Value
	 * @param primKeyID
	 *            Primary Key Value for filtering
	 * @param dataType
	 *            Data Type of dbColumn {String, Integer, Double}
	 * @return true if update was successful
	 */
	public boolean updateDBTableValueParam(String dbTable, String dbColumn,
			String tblIDCol, Object value, int primKeyID, String dataType) {
		boolean isSuccess = false;
		String updateSQL = "UPDATE " + dbTable + " SET " + dbColumn
				+ " = ? WHERE " + tblIDCol + " = ?";
		PreparedStatement pstmt;
		try {
			pstmt = this.conn.prepareStatement(updateSQL);
			if(dataType=="String"){
				pstmt.setString(1, (String)value);
			}
			if(dataType=="Integer"){
				pstmt.setInt(1, new Integer(value.toString()).intValue());
			}
			if(dataType=="Double"){
				pstmt.setDouble(1, new Double(value.toString()).doubleValue());
			}
			
			pstmt.setInt(2, primKeyID);
			pstmt.execute();
			isSuccess = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			isSuccess = false;
		}
		return isSuccess;
	}
	/**
	 * 
	 * Deletes a row where dbColumn = value and tblIDCol = primKeyID
	 * 
	 * @param dbTable
	 *            Table Name to be updated
	 * @param dbColumn
	 *            Table Column to be updated
	 * @param tblIDCol
	 *            Primary Key Column
	 * @param value
	 *            New Value
	 * @param primKeyID
	 *            Primary Key Value for filtering
	 * @return true if update was successful
	 */
	public boolean deleteDBTableValueParam(String dbTable, String dbColumn,
			String tblIDCol, String value, int primKeyID) {
		boolean isSuccess = false;
		String updateSQL = "DELETE FROM  " + dbTable + " WHERE " + dbColumn
				+ " = ? AND " + tblIDCol + " = ?";
		PreparedStatement pstmt;
		try {
			pstmt = this.conn.prepareStatement(updateSQL);
			pstmt.setString(1, value);
			pstmt.setInt(2, primKeyID);
			pstmt.execute();
			isSuccess = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			isSuccess = false;
		}
		return isSuccess;
	}
}